import sqlite3

class DbManager(object):

    def __init__(self, *args):
        self.db = sqlite3.connect(*args)
        self.cursor = self.db.cursor()

    def __enter__(self):
        return self.cursor

    def __exit__(self, types, value, traceback):
        self.db.commit()
        return False

    def __del__(self):
        self.db.commit()
        self.db.close()

    def switchDb(self, *args):
        self.db.close()

        self.db = sqlite3.connect(*args)
        self.cursor = self.db.cursor()

    def createTable(self, tableString):
        self.cursor.execute(tableString)
        self.db.commit()

    def commitAndClose(self):
        self.db.commit()
        self.db.close()

class SQLiteDb(DbManager):

    caseiPerson = """
    CREATE TABLE IF NOT EXISTS caseiPerson(
        id INTEGER PRIMARY KEY autoincrement,
        city TEXT,
        unit TEXT,
        person TEXT,
        certs TEXT,
        has TEXT,
        tm TEXT
    )"""

    def __init__(self, database='', *args):
        super().__init__(database, *args)
        self.initDb()

    def initDb(self):
        self.createTable(self.caseiPerson)

    def addPerson(self, city, unit, person, certs, has, tm):
        self.cursor.execute("""INSERT INTO caseiPerson 
                (city, unit, person, certs, has, tm) VALUES 
                ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')
                """.format(city, unit, person, certs, has, tm))
        self.db.commit()

    def updatePerson(self, unit, person, certs, has):
        self.cursor.execute("""UPDATE caseiPerson 
                SET certs = '{0}', has = '{1}'  
                WHERE unit='{2}' AND person = '{3}'
                """.format(certs, has, unit, person))

        self.db.commit()

    def delPerson(self, uid):
        self.cursor.execute("""DELETE FROM caseiPerson WHERE id={}""".format(uid))
        self.db.commit()

    def fetchPersons(self, limit = 50, page = 1):
        self.cursor.execute("""SELECT * FROM caseiPerson 
            ORDER BY id DESC LIMIT {0} OFFSET {1}""".format(limit, (page - 1)*limit))
        return self.cursor.fetchall()
